package com.tust.dao;

import com.tust.bean.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {

    // 声明一个连接对象引用 用来接收与数据库连接的connection实例对象
    private Connection connection;

    // 声明一个PreparedStatement对象引用
    private PreparedStatement statement;

    public StudentDao(Connection connection) {
        // 获取到连接实例对象与数据库建立连接
        this.connection = connection;
    }

    /**
     * 添加学生
     * @param student
     * @return
     */
    public boolean insert(Student student) {

        try {

            // 检查是否已经存在此学号
            statement = connection.prepareStatement("SELECT id FROM student WHERE id = ?");
            statement.setInt(1,student.getSid());
            try (ResultSet resultSet = statement.executeQuery()) { // 自动关闭ResultSet接口

                // 如果已经存在 则修改 否则新增
                if (resultSet.next()) {

                    statement = connection.prepareStatement("UPDATE student SET name = ?, age = ?, phone = ?, is_delete = 0 WHERE id = ?");

                    statement.setString(1,student.getSname());
                    statement.setInt(2,student.getSage());
                    statement.setString(3,student.getSphone());
                    statement.setInt(4,resultSet.getInt(1));

                } else {
                    // 不存在 直接新增
                    statement = connection.prepareStatement("INSERT INTO student (id, name, age, phone) VALUES (?, ?, ?, ?)");

                    statement.setInt(1, student.getSid());
                    statement.setString(2, student.getSname());
                    statement.setInt(3, student.getSage());
                    statement.setString(4,student.getSphone());

                }

                statement.executeUpdate();

                return true;

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }


        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除所有学生
     * @return
     */
    public boolean delete() {

        try {

            statement = connection.prepareStatement("UPDATE student SET is_delete = 1");
            statement.executeUpdate();

            return true;

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除指定学生
     * @param sid
     * @return
     */
    public boolean delete(int sid) {

        // 首先判断该学生是否存在，如果存在删除，不存在报错
        if (query(sid) != null) {

            try {

                statement = connection.prepareStatement("UPDATE student SET is_delete = 1 WHERE id = ?");

                statement.setInt(1, sid);
                statement.executeUpdate();

                return true;

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }

            return false;

        } else {
            System.out.println(sid + "学生不存在！");
            return false;
        }
    }

    /**
     * 更新学生信息
     * @param student
     * @return
     */

    public boolean update(Student student) {

        // 修改之前先判断该学生存不存在 如果存在则修改，否则报错
        if (query(student.getSid()) != null) {
            try {
                statement = connection.prepareStatement("UPDATE student SET name = ?, age = ?, phone = ? WHERE id = ? AND is_delete = 0");
                statement.setString(1,student.getSname());
                statement.setInt(2,student.getSage());
                statement.setString(3,student.getSphone());
                statement.setInt(4,student.getSid());
                statement.executeUpdate();
                return true;
            } catch (SQLException e) {
                System.out.println(e.getMessage());
                return false;
            }
        } else {
            System.out.println("该学生不存在，请检查后重新提交！");
            return false;
        }
    }

    /**
     * 查询指定学生
     * @param sid
     * @return
     */

    public Student query(int sid) {

        Student student = null;

        try {
            statement = connection.prepareStatement("SELECT id, name, age, phone FROM student WHERE id = ? AND is_delete = 0");
            statement.setInt(1,sid);
            try (ResultSet resultSet = statement.executeQuery()) { // 自动关闭ResultSet接口

                if (resultSet.next()) {
                    student = new Student();
                    student.setSid(resultSet.getInt(1));
                    student.setSname(resultSet.getString(2));
                    student.setSage(resultSet.getInt(3));
                    student.setSphone(resultSet.getString(4));
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return student;
    }

    /**
     * 查询所有学生
     * @return
     */

    public List<Student> query() {
        List<Student> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT id, name, age, phone FROM student WHERE is_delete = 0");
            try (ResultSet resultSet= statement.executeQuery()) {

                while(resultSet.next()){
                    Student student = new Student();
                    student.setSid(resultSet.getInt(1));
                    student.setSname(resultSet.getString(2));
                    student.setSage(resultSet.getInt(3));
                    student.setSphone(resultSet.getString(4));
                    list.add(student);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

    /**
     * 关闭数据库连接中的statement 对象 释放相关资源
     */

    public void close() {

        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
